/***********************************************************************************************************************************
 * Audit_Linked_Server.sql
 * Find routines and views that use the specified linked server.
 */
DECLARE
	@linked_server sysname = N'',
	@tsql NVARCHAR(MAX);

BEGIN TRY DROP TABLE #Results END TRY BEGIN CATCH END CATCH;
CREATE TABLE #Results (
	[Catalog]	sysname,
	[Schema]	sysname,
	[Name]		sysname,
	[Type]		sysname);

SET NOCOUNT ON;
DECLARE list CURSOR FAST_FORWARD FOR SELECT N'USE [' + database_name + N']; ' FROM master.DBAdmin.DBList('@');
OPEN list;
 WHILE (0=0) BEGIN
  FETCH NEXT FROM list INTO @tsql;
  IF @@FETCH_STATUS <> 0 BREAK;
  SET @tsql+= N'INSERT INTO #Results SELECT ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ''%?[`].]%'' ESCAPE ''`'' OR ROUTINE_DEFINITION LIKE ''%AT ?%''; ';
  SET @tsql+= N'INSERT INTO #Results SELECT   TABLE_CATALOG,   TABLE_SCHEMA,   TABLE_NAME,     ''VIEW'' FROM INFORMATION_SCHEMA.VIEWS    WHERE VIEW_DEFINITION    LIKE ''%?[`].]%'' ESCAPE ''`'';';
  SET @tsql= REPLACE(@tsql, N'?', @linked_server);
  EXECUTE (@tsql);
END;
CLOSE list;
DEALLOCATE list;
SET NOCOUNT OFF;

SELECT [Type], [Catalog] + '.' + [Schema] + '.' + [Name] AS [Result]
  FROM #Results
  ORDER BY [Type], [Catalog], [Schema], [Name];
GO
